
global MyPath "/Users/matthieugomez/Dropbox/Projects/APR"
global WorldscopePath "$MyPath/Worldscope"


/* get country and industry of each firm */
use code ITEM6027 ITEM7021 using "$WorldscopePath/wrds_ws_company", clear
rename ITEM6027 countrycode
rename ITEM7021 industry
keep code countrycode industry
* drop public utilities and financial firms
drop if inrange(industry, 4900, 4999) | inrange(industry, 6000, 6999)
sort code
tempfile temp_code
save `temp_code'


/* get value from firm level dataset */
use code year_ freq ITEM7230 ITEM7210 ITEM1251 ITEM4052 ITEM5376    ITEM7220 ITEM7250 ITEM7240 ITEM1001 using "$WorldscopePath/wrds_ws_funda", clear
sort code
merge m:1 code using `temp_code', keep(matched) sorted nogen
* step necessary to avoid duplicates. "A" means unrestated annual financials
keep if freq == "A"
rename year_ year
gen asset_book = ITEM7230
gen equity_book = ITEM7220
gen debt = asset_book - equity_book
gen equity_market = ITEM7210 
gen netincome = ITEM7240
gen netincome_localcurrency = ITEM1001
* dividends and interest are in local currency so need to modify it
gen cashflow = (ITEM4052 + ITEM1251) * netincome / netincome_localcurrency 
replace cashflow = (ITEM5376 + ITEM1251) * netincome / netincome_localcurrency  if missing(cashflow)
gen asset_market =  equity_market + debt
gen cf_yield = cashflow  / asset_market
keep if asset_market > 0 & !missing(asset_market) & !missing(cashflow)
collapse (mean) cf_yield (rawsum) asset_market [w = asset_market], by(countrycode year)
export delimited using "$MyPath/data/worldscope/worldscope.csv", replace


